import sqlite3


class SQLiteCRUD:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name, check_same_thread=False)
        self.cursor = self.conn.cursor()

    def create_table(self, table_name, columns):
        create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join(columns)})"
        print(create_table_query)
        self.cursor.execute(create_table_query)
        self.conn.commit()

    def insert_data(self, table_name, data):
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['?'] * len(data))
        insert_query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        print(insert_query)
        self.cursor.execute(insert_query, tuple(data.values()))
        self.conn.commit()

    def select_data(self, table_name, conditions=None):
        if conditions:
            select_query = f"SELECT * FROM {table_name} WHERE {conditions}"
        else:
            select_query = f"SELECT * FROM {table_name}"
        print(select_query)
        self.cursor.execute(select_query)
        return self.cursor.fetchall()

    def update_data(self, table_name, data, conditions):
        set_clause = ', '.join([f"{key}=?" for key in data.keys()])
        where_clause = ', '.join([f"{key}=?" for key in conditions.keys()])
        update_query = f"UPDATE {table_name} SET {set_clause} WHERE {where_clause}"
        values = tuple(data.values()) + tuple(conditions.values())
        print(update_query, values)
        self.cursor.execute(update_query, values)
        self.conn.commit()

    def delete_data(self, table_name, conditions):

        where_clause = ', '.join([f"{key}=?" for key in conditions.keys()])
        delete_query = f"DELETE FROM {table_name} WHERE {where_clause}"
        values = tuple(tuple(conditions.values()))
        print(delete_query, values)
        self.cursor.execute(delete_query, values)
        self.conn.commit()

    def get_table_columns(self, table_name):
        """获取表的字段名"""
        self.cursor.execute(f"PRAGMA table_info({table_name})")
        columns = [info[1] for info in self.cursor.fetchall()]
        return columns

    def drop_table(self, table_name):
        """删除表"""
        drop_table_query = f"DROP TABLE IF EXISTS {table_name}"
        self.cursor.execute(drop_table_query)
        self.conn.commit()

    def get_tabl_list(self):
        sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
        self.cursor.execute(sql)
        values = self.cursor.fetchall()
        return values

    def close_connection(self):
        self.conn.close()

    # 使用示例


if __name__ == "__main__":
    crud = SQLiteCRUD("example.db")

    # 创建表
    crud.create_table(
        table_name="users",
        columns=["id INTEGER PRIMARY KEY", "name TEXT", "age INTEGER"]
    )

    # 创建表
    crud.create_table(
        table_name="info",
        columns=["id INTEGER PRIMARY KEY", "name TEXT", "describe TEXT"]
    )

    # 插入数据
    crud.insert_data(
        table_name="users",
        data={"name": "Alice", "age": 25}
    )
    crud.insert_data(
        table_name="users",
        data={"name": "Bob", "age": 30}
    )

    # 查询数据
    users = crud.select_data(table_name="users")
    for user in users:
        print(user)

        # 更新数据
    crud.update_data(
        table_name="users",
        data={"age": 26},
        conditions={"name": "Alice"}
    )

    # 删除数据
    crud.delete_data(
        table_name="users",
        conditions={"name": "Bob"}
    )

    print("获取表信息：")
    print(crud.get_tabl_list())

    # 关闭连接
    crud.close_connection()